Mega Code Archive - Oracle PLSQL Tutorial - Function Procedure Packages

Function Procedure Packages
1) A Package Specification and its body
2) A package to calculate your age
3) A pipelined Table Function that returns a PLSQL type
4) A procedure block
5) A Subtypes Example
6) Add day to month
7) All packages can be recompiled by using the Oracle utility dbms_utility
8) Alter procedure p compile and check the dba_ddl_locks again
9) Audit package
10) Autonomous Insert or nonautomous insert
11) Call a trigger in procedure
12) Call function in a Package
13) Calling a Cursor Declared in a Different Package
14) Calling a Function
15) Calling a Function for a table
16) Calling a Procedure
17) Calling Functions and Procedures in a Package
18) Calling myProc
19) Calls procedure in a package
20) Check the code version
21) Compare date offset in a function
22) Controlling access to packages
23) Convert Comma-separated values to table collection
24) Counter package
25) Create a function to return a employee record accept employee numner return all fields
26) Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME
27) Create a package to handle display, update, delete and insert operations
28) Create a procedure to count employees
29) Create a simple function
30) CREATE OR REPLACE a function
31) Create or replace a procedure
32) Create procedure for AUTHID CURRENT_USER
33) Create procedure for displaying long text line by line
34) Creating a Function to deal with business rules
35) Creating a Package Body
36) Creating a Package Specification
37) Creating a procedure
38) Creating a Stored Procedure for table update
39) Creating Packages and call its functions
40) Creating stored procedures
41) Crosss reference between two packages
42) Cursor variable in a package
43) Datatype family of parameters
44) Date calculation
45) Date time calculation function
46) Decrease salary with user procedure
47) Define constant in a package
48) Define function with NUMBER type parameter
49) Define inner function to calculate age
50) Define your own varchar to date function
51) Defining Formal Parameters
52) Demmonstrate the creation and execution of a simple PLSQL procedure
53) Demonstrate returning a record
54) Demonstrate the behavior of IN, OUT, and IN OUT parameter modes
55) Demonstrate using a packaged ref cursor for passing sets
56) Demonstration of a nested procedure block
57) Dependency Example
58) Describe a user-defined function
59) Disable trigger
60) Dont display lines longer than 80 characters
61) Dropping a Package
62) Dropping a Procedure
63) Dynamically create packages
64) Dynamically perform any DDL statements from within your normal PLSQL processing
65) Exceptions in Subprograms
66) Execuate the same SQL in two ways
67) Execute a procedure
68) Exit a function
69) Format money
70) Forward Declarations
71) Forward Referencing
72) Function to convert celsius to fahrenheit
73) Function to convert fahrenheit to celsius
74) Function without parameter
75) Function without parameters
76) Functions
77) Generate Random number
78) Generic function utilizing dynamic SQL to return the number of rows in the specified table
79) Get circle area
80) Get full name package
81) Get nearest day
82) Get the Max date
83) Get the next business day
84) Globals Stored in a Package
85) Call a Function from PLSQL With or Without Parameters
86) IN OUT difference
87) IN OUT NOCOPY
88) IN OUT parameters
89) IN Parameters in action
90) Insert debug line to the stored procedure
91) Insert debug line to the stored procedure with DBMS_OUTPUT PUT_LINE
92) Listing Stored Procedure Information
93) Local Subprograms
94) Mark a save point in a procedure
95) Match a date format
96) Method overload
97) Mixed Name and Position Notation Calls
98) Mixed notation
99) More than one match was found so Oracle raised a special error
100) Multiple RETURN Statements
101) MyProc procedure with a default value for both parameters
102) MyProc procedure with a default value for one parameter
103) Named notation
104) Names of parameters
105) Nested function in anonymous block
106) Number of parameters
107) Out parameter
108) OUT Parameters
109) Output to the SQL plus
110) Overloading calls
111) Overloading Packaged Subprograms
112) Package constant variable
113) Package declaration and body
114) Package Function with PRAGMA AUTONOMOUS_TRANSACTION
115) Package initialization
116) Package level cursor variable
117) Package method overloading
118) Package RECURSION
119) Package State
120) Package with only one function
121) Package with two procedures
122) Packages
123) Parameter Default Values
124) Parameter Modes
125) Pass ROWTYPE to a procedure
126) Passing parameters to procedures
127) Performance improvement of NOCOPY
128) PLS-00452
129) PLW-05005
130) Positional Notation
131) Positional vs named parameter passing
132) Pragma AUTONOMOUS_TRANSACTION and lock
133) Pre-filled table collection of varchars in a package
134) Private field
135) Private Versus Public Package Objects
136) Procedure create_order
137) Procedure with pragma autonomous_transaction
138) Procedures
139) Process date value
140) Product check
141) Recompile objects is to use the ALTER object type object name COMPILE command
142) Recompiling Packages
143) Re-creating a Procedure By Using OR REPLACE
144) Recursion
145) Recursive functions
146) Reference fields and methods in package
147) Reference method from another package
148) Removes all numeric digits from the string passed in
149) Return a table collection
150) Return column type
151) Return cursor from function
152) Return date value from a function
153) Return from a procedure
154) Return number from a function
155) Return Types
156) Return user-defined type
157) Return value from a function
158) Returning a list based on parameters
159) Returning values with functions
160) Returns the total from an order number being passed in
161) Save Debugging Time with WHEN OTHERS and store an Exception Message
162) Serially Reusable Packages
163) Show error on a function
164) Specifying procedure or function parameters Positional notation
165) Statements after Return will not be executed
166) Storing PLSQL function in the Database
167) Storing PLSQL procedure in the Database
168) String between function
169) Table collection type parameter
170) Table Count function
171) Test unit for package scopes
172) The behavior of NOCOPY
173) The FullName Function
174) The hint NOCOPY is applicable only to OUT and IN OUT types of variables
175) There are some restrictions on overloading
176) There are three types of formal parameters in subprograms
177) Timer package
178) To number or null
179) Update table in PLSQL procedure
180) Use EXEC to call a procedure in a package
181) Use function to check passwords
182) Use function to initialize the package level variable
183) Use IFELSIFELSE to verify the input parameter
184) Use mixed notation to avoid the second parameter, but keep the first and third
185) Use of get and set prefixes
186) Use package member variable to pass value
187) Use package method in a procedure
188) Use package to define variable and use across code blocks
189) Use ROWTYPE as the parameter
190) Using Named Notation
191) Using RESTRICT_REFERENCES in a Package
192) Using stored functions in SQL statements, function getName
193) Validate all objects in a schema
194) Viewing Errors in a Procedure
195) Word count function
196) Wrapping a task into a procedure
197) You can pass variables by reference, even in PLSQL